--use master
--go
drop database TBENGLISH
go

create database TBENGLISH
go
use TBENGLISH
go

create table QUESTION
(
	QID int not null,
	Content nvarchar(1024) not null,
	DiffID int not null,
	
	constraint PK_QID primary key (QID)
)
go

create table ANSWER
(
	AID int not null,
	Content nvarchar(1024) not null,
	IsTrue bit not null,
	QID int not null,
	
	constraint PK_AID primary key (AID)
)
go

create table DIFFICULTY
(
	DiffID int not null,
	DifficultName nvarchar(50) not null,
	Experience bigint not null,
	
	constraint PK_DIFFID primary key (DIFFID)
)
go

create table ACCOUNT
(
	UserID nvarchar(50) not null,
	UserName nvarchar(50) not null,
	Password nvarchar(50) not null,
	
	Experience bigint not null,
	Level int not null,
	
	TypeID int not null,
	
	constraint PK_NAME primary key (UserID)
)
go

create table ACCOUNTTYPE
(
	TypeID int not null,
	Description nvarchar(1024) not null,
	
	constraint PK_TYPEID primary key (TypeID)
)
go

create table EXPERIENCE
(
	Level int not null,
	Experience bigint not null,
	
	constraint PK_LEVEL primary key (Level)
)
go

create table RELATION
(
	UserName1 nvarchar(50) not null,
	UserName2 nvarchar(50) not null,
	
	RelationID int not null,
	
	constraint PK_USERNAME1_USERNAME2 primary key (UserName1, UserName2)
)
go

create table RELATIONSHIP
(
	RelationID int not null,
	Description nvarchar(1024) not null,
	
	constraint PK_RELATIONID primary key (RelationID)
)
go

create table ROOM
(
	RoomID int not null,
	Name nvarchar(50) not null,
	
	-- room setting
	Password nvarchar(50),
	Limit int not null,
	-- more
	
	constraint PK_ROOMID primary key (RoomID)
)
go

create table REQUEST
(
	RequestFrom nvarchar(50) not null,
	RequestTo nvarchar(50) not null,
	Content nvarchar(100)
	
	constraint PK_RequestFrom_To primary key ( RequestFrom, RequestTo)
)

create table CHATMESSAGE
(
	Sender nvarchar(50) not null,
	Receiver nvarchar(50) not null,
	SendTime DateTime not null,
	Content nvarchar(1024)
	
	constraint PK_ChatMessage_Sender_Receiver_SendTime primary key (Sender, Receiver, SendTime)
)

-- KHOA NGOAI
alter table QUESTION add constraint FK_QUESTION_DIFFICULTY foreign key (DiffID) references DIFFICULTY(DiffID)

alter table ANSWER add constraint FK_ANSWER_QUESTION foreign key (QID) references QUESTION(QID)

alter table ACCOUNT add constraint FK_ACCOUNT_EXPERIENCE foreign key (Level) references EXPERIENCE(Level)
alter table ACCOUNT add constraint FK_ACCOUNT_ACCOUNTTYPE foreign key (TypeID) references ACCOUNTTYPE(TypeID)

alter table RELATION add constraint FK_RELATION_ACCOUNT_USERNAME1 foreign key (UserName1) references ACCOUNT(UserID)
alter table RELATION add constraint FK_RELATION_ACCOUNT_USERNAME2 foreign key (UserName2) references ACCOUNT(UserID)
alter table RELATION add constraint FK_RELATION_RELATIONSHIP foreign key (RelationID) references RELATIONSHIP(RelationID)

--alter table ROOMUSER add constraint FK_ROOMUSER_ROOM foreign key (RoomID) references ROOM(RoomID)
--alter table ROOMUSER add constraint FK_ROOMUSER_ACCOUNT foreign key (UserName) references ACCOUNT(Name)

alter table REQUEST add constraint FK_REQUEST_ACCOUNTFROM foreign key (RequestFrom) references ACCOUNT(UserID)
alter table REQUEST add constraint FK_REQUEST_ACCOUNTTO foreign key (RequestTo) references ACCOUNT(UserID)

alter table CHATMESSAGE add constraint FK_CHATMESSAGE_ACCOUNTSENDER foreign key (Sender) references ACCOUNT(UserID)

CREATE TRIGGER TRIGGER_RECEIVER_CHATMESSAGE_REFERS_USERID_ACCOUNT_OR_ROOMID_ROOM
ON CHATMESSAGE
for insert as
begin
	if ((NOT EXISTS
			(
			SELECT UserID FROM ACCOUNT acc WHERE EXISTS 
				(
				SELECT Sender FROM INSERTED ins WHERE ins.Receiver = acc.UserID
				)
			)
		)
		AND (NOT EXISTS
			(
			SELECT RoomID FROM ROOM room WHERE EXISTS
				(
				SELECT Sender FROM INSERTED ins WHERE ins.Receiver = room.RoomID
				)
			)
		)
		)
		rollback transaction
end
go